import time
from . import ConnPG
import psycopg2
from sanic.log import logger

class SkuWarehouseData:
    #拼接sku_id_list成为sql的一部分
    def jont_sku_id_list_to_sql(self, sku_id_list, id_name="id"):

        id_str = ''
        for i in sku_id_list:

            id_str = id_str + f'or {id_name}={i} '

        return id_str[2:]

    #给出一个sku_id_list 返回对应的sku_info字典
    async def select_sku_info_for_id_list(self, sku_id_list):

        id_str = self.jont_sku_id_list_to_sql(sku_id_list)

        if id_str == '':

            return {}

        sql = "select * from view_sku_info where " + id_str

        result = await ConnPG().fetchall(sql)

        res =  {i['id']: dict(i) for i in result}

        return res

    #给出一个sku_id_list 返回对应的sku属性字典
    async def select_sku_attribute_for_id_list(self, sku_id_list):

        result_dict = dict.fromkeys(sku_id_list)

        id_str = self.jont_sku_id_list_to_sql(sku_id_list, 'sku_id')

        if id_str == '':

            return {}

        sql = "select * from view_sku_attribute where" + id_str


        result = await ConnPG().fetchall(sql)

        for item in result:
            key = item['sku_id']
            if result_dict[key] == None:

                result_dict[key] = []

            result_dict[key].append(dict(item))

        return result_dict

    #获取对应仓库的所有sku进出总数
    async def select_sku_account_for_warehouse_id(self, warehouse_id):

        sql = "select * from sum_view_sku_account where warehouse_id=%s"

        result = await ConnPG().fetchall(sql, (warehouse_id,))
        # print(result)
        sku_id_list = list({i['sku_id'] for i in result})

        res = dict.fromkeys(sku_id_list)
        count = dict.fromkeys(sku_id_list)

        for i in result:
            key = i['sku_id']
            if res[key] == None:
                res[key] = []
            res[key].append(dict(i))


        # print(res)

        for key, value in res.items():
            in_num = 0
            out_num = 0

            for i in value:
                if i['in_or_out'] == True:
                    in_num = i['sum']
                if i['in_or_out'] == False:
                    out_num = i['sum']

            count[key] = in_num - out_num

        return {
            'sku_id_list':list(sku_id_list),
            'count': count
        }

    async def select_all_sku_detail_by_warehouse_id(self, warehouse_id):

        if warehouse_id == None:  

            return {
                'status': -1,
                'errorCode': 303102,
                'errorMsg': '缺少warhouse_id',
                'resultBody': None,
            }

        info = await self.select_sku_account_for_warehouse_id(warehouse_id)
        info['attribute'] = await self.select_sku_attribute_for_id_list(info['sku_id_list'])
        info['sku_info'] = await self.select_sku_info_for_id_list(info['sku_id_list'])

    
        # print(info)
        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': info,
        }








    #     #参数检查

    #     if not warehouse_name:

    #         return {
    #             'status': -1,
    #             'errorCode': 300100,
    #             'errorMsg': '库房名不能为空',
    #             'resultBody': None
    #         }

    #     sql = 'INSERT INTO warehouse(warehouse_name) VALUES (%s)'

    #     try:
    #         await ConnPG().execute(sql, (warehouse_name,))
    #     except psycopg2.errors.UniqueViolation:

    #         return {
    #             'status': -1,
    #             'errorCode': 300101,
    #             'errorMsg': '当前库名已经存在',
    #             'resultBody': None,
    #         }
    #     else:

    #         return {
    #             'status': 1,
    #             'errorCode': None,
    #             'errorMsg': None,
    #             'resultBody': "库房添加成功",
    #         }

    # async def select_all_warehouse(self):#查看所有库房

    #     sql = 'select * from warehouse'

    #     result = await ConnPG().fetchall(sql)

    #     return {
    #         'status': 1,
    #         'errorCode': None,
    #         'errorMsg': None,
    #         'resultBody': result,
    #     }


    # async def select_all_warehouse_by_duty(self, duty_id):

    #     if duty_id == None or not duty_id.isdecimal():

    #         return {
    #             'status': -1,
    #             'errorCode': 300107,
    #             'errorMsg': '却少duty_id参数',
    #             'resultBody': None
    #         }



    #     sql_ = "select * from view_duty_warehouse where duty_id=%s"

    #     duty_result = await ConnPG().fetchall(sql_, (duty_id,))

    #     warehouse_list = [i['warehouse_id'] for i in duty_result]

    #     warehouse_list_str = ''

    #     for i in warehouse_list:

    #         warehouse_list_str = warehouse_list_str + f'or id={i} '

    #     sql = "select * from warehouse where " + warehouse_list_str[2:]

    #     result = await ConnPG().fetchall(sql)
    #     print(result)
    #     return {
    #         'status': 1,
    #         'errorCode': None,
    #         'errorMsg': None,
    #         'resultBody': result,
    #     }




    # async def del_warehouse(self, warehouse_id):#删除warehouse

    #     #参数检查

    #     if not warehouse_id or not warehouse_id.isdecimal:

    #         return {
    #             'status': -1,
    #             'errorCode': 300100,
    #             'errorMsg': '库房id不能为空且为整数类型',
    #             'resultBody': None
    #         }

    #     sql = 'DELETE FROM warehouse WHERE id=%s'

    #     try:
    #         await ConnPG().execute(sql, (warehouse_id,))
    #     except psycopg2.errors.ForeignKeyViolation as e:

    #         # print(e)

    #         return {

    #             'status': -1,
    #             'errorCode': 300112,
    #             'errorMsg': str(e),
    #             'resultBody': None,
    #         }
    #     else:
        
    #         return {
    #             'status': 1,
    #             'errorCode': None,
    #             'errorMsg': None,
    #             'resultBody': "公司或项目删除成功",                                     
    #         }

    # async def update_warehouse(self, warehouse_id, warehouse_name):#更新库房名称

    #     #参数检查
    #     if not all([warehouse_id, warehouse_name]) or not warehouse_id.isdecimal():


    #         return {
    #             'status': -1,
    #             'errorCode': 300100,
    #             'errorMsg': '接口有两个必传参数且warehouse_id为整数',
    #             'resultBody': None
    #         }

    #     sql = "UPDATE warehouse SET warehouse_name=%s WHERE id=%s"


    #     await ConnPG().execute(sql, (warehouse_name,warehouse_id))

    
    #     return {
    #         'status': 1,
    #         'errorCode': None,
    #         'errorMsg': None,
    #         'resultBody': "名称修改成功",
    #     }

    